# -*- coding: utf-8 -*-
"""
存储适配器 - 支持MySQL和内存存储的自动切换
"""
import json
import threading
from datetime import datetime
from typing import Dict, List, Optional, Any, Union
from sqlalchemy.orm import Session
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError

from .database import Base, LaixinRecord, NetRecord, SessionLocal


class MemoryStorage:
    """内存存储实现"""
    
    def __init__(self):
        self._lock = threading.RLock()
        self._laixin_records: Dict[str, Dict] = {}
        self._net_records: Dict[str, Dict] = {}
        self._next_id = 1
    
    def _get_next_id(self) -> int:
        """获取下一个ID"""
        with self._lock:
            current_id = self._next_id
            self._next_id += 1
            return current_id
    
    def create_laixin_record(self, **kwargs) -> Dict:
        """创建来信件记录"""
        with self._lock:
            record_id = self._get_next_id()
            record = {
                'id': record_id,
                'bh': kwargs.get('bh'),
                'txm': kwargs.get('txm'),
                'start_time': kwargs.get('start_time', datetime.now()),
                'status': kwargs.get('status', '处理中'),
                'process_time': kwargs.get('process_time', 0.0),
                'case_instruction': kwargs.get('case_instruction'),
                'response': kwargs.get('response'),
                'error_info': kwargs.get('error_info'),
                'is_delete': kwargs.get('is_delete', 0)
            }
            self._laixin_records[record['bh']] = record
            return record
    
    def create_net_record(self, **kwargs) -> Dict:
        """创建网络件记录"""
        with self._lock:
            record_id = self._get_next_id()
            record = {
                'id': record_id,
                'bh': kwargs.get('bh'),
                'ysbh': kwargs.get('ysbh'),
                'start_time': kwargs.get('start_time', datetime.now()),
                'status': kwargs.get('status', '处理中'),
                'process_time': kwargs.get('process_time', 0.0),
                'case_instruction': kwargs.get('case_instruction'),
                'fyrs': kwargs.get('fyrs'),
                'cfj': kwargs.get('cfj'),
                'bfyrs': kwargs.get('bfyrs'),
                'response': kwargs.get('response'),
                'error_info': kwargs.get('error_info'),
                'is_delete': kwargs.get('is_delete', 0)
            }
            self._net_records[record['bh']] = record
            return record
    
    def update_laixin_record(self, bh: str, **kwargs) -> Optional[Dict]:
        """更新来信件记录"""
        with self._lock:
            if bh in self._laixin_records:
                record = self._laixin_records[bh]
                for key, value in kwargs.items():
                    if key in record:
                        record[key] = value
                return record
            return None
    
    def update_net_record(self, bh: str, **kwargs) -> Optional[Dict]:
        """更新网络件记录"""
        with self._lock:
            if bh in self._net_records:
                record = self._net_records[bh]
                for key, value in kwargs.items():
                    if key in record:
                        record[key] = value
                return record
            return None
    
    def get_laixin_records(self, filters: Dict = None) -> List[Dict]:
        """获取来信件记录列表"""
        with self._lock:
            records = list(self._laixin_records.values())
            
            if filters:
                filtered_records = []
                for record in records:
                    if record.get('is_delete', 0) != 0:
                        continue
                    
                    # 应用过滤条件
                    if 'date' in filters and filters['date']:
                        record_date = record['start_time'].strftime('%Y-%m-%d')
                        if record_date != filters['date']:
                            continue
                    
                    if 'bh' in filters and filters['bh']:
                        if filters['bh'].lower() not in record.get('bh', '').lower():
                            continue
                    
                    if 'status' in filters and filters['status']:
                        if record.get('status') != filters['status']:
                            continue
                    
                    if 'txm' in filters and filters['txm']:
                        response = record.get('response')
                        if not response or filters['txm'] not in response:
                            continue
                    
                    filtered_records.append(record)
                
                return sorted(filtered_records, key=lambda x: x['start_time'], reverse=True)
            
            return sorted([r for r in records if r.get('is_delete', 0) == 0], 
                         key=lambda x: x['start_time'], reverse=True)
    
    def get_net_records(self, filters: Dict = None) -> List[Dict]:
        """获取网络件记录列表"""
        with self._lock:
            records = list(self._net_records.values())
            
            if filters:
                filtered_records = []
                for record in records:
                    if record.get('is_delete', 0) != 0:
                        continue
                    
                    # 应用过滤条件
                    if 'date' in filters and filters['date']:
                        record_date = record['start_time'].strftime('%Y-%m-%d')
                        if record_date != filters['date']:
                            continue
                    
                    if 'bh' in filters and filters['bh']:
                        if filters['bh'].lower() not in record.get('bh', '').lower():
                            continue
                    
                    if 'status' in filters and filters['status']:
                        if record.get('status') != filters['status']:
                            continue
                    
                    if 'ysbh' in filters and filters['ysbh']:
                        response = record.get('response')
                        if not response or filters['ysbh'] not in response:
                            continue
                    
                    filtered_records.append(record)
                
                return sorted(filtered_records, key=lambda x: x['start_time'], reverse=True)
            
            return sorted([r for r in records if r.get('is_delete', 0) == 0], 
                         key=lambda x: x['start_time'], reverse=True)
    
    def delete_laixin_record(self, bh: str) -> bool:
        """逻辑删除来信件记录"""
        with self._lock:
            if bh in self._laixin_records:
                self._laixin_records[bh]['is_delete'] = 1
                return True
            return False
    
    def delete_net_record(self, bh: str) -> bool:
        """逻辑删除网络件记录"""
        with self._lock:
            if bh in self._net_records:
                self._net_records[bh]['is_delete'] = 1
                return True
            return False
    
    def batch_delete_laixin_records(self, bhs: List[str]) -> int:
        """批量逻辑删除来信件记录"""
        with self._lock:
            deleted_count = 0
            for bh in bhs:
                if bh in self._laixin_records:
                    self._laixin_records[bh]['is_delete'] = 1
                    deleted_count += 1
            return deleted_count
    
    def batch_delete_net_records(self, bhs: List[str]) -> int:
        """批量逻辑删除网络件记录"""
        with self._lock:
            deleted_count = 0
            for bh in bhs:
                if bh in self._net_records:
                    self._net_records[bh]['is_delete'] = 1
                    deleted_count += 1
            return deleted_count
    
    def get_laixin_stats(self) -> Dict[str, int]:
        """获取来信件统计信息"""
        with self._lock:
            records = [r for r in self._laixin_records.values() if r.get('is_delete', 0) == 0]
            return {
                'processed_count': len(records),
                'successful_count': len([r for r in records if r.get('status') == '成功']),
                'failed_count': len([r for r in records if r.get('status') == '失败'])
            }
    
    def get_net_stats(self) -> Dict[str, int]:
        """获取网络件统计信息"""
        with self._lock:
            records = [r for r in self._net_records.values() if r.get('is_delete', 0) == 0]
            return {
                'processed_count': len(records),
                'successful_count': len([r for r in records if r.get('status') == '成功']),
                'failed_count': len([r for r in records if r.get('status') == '失败'])
            }


class StorageAdapter:
    """存储适配器 - 自动选择MySQL或内存存储"""
    
    def __init__(self, database_url: str = ""):
        self.database_url = database_url
        self.use_mysql = False
        self.memory_storage = MemoryStorage()
        self.db_session = None
        
        # 尝试连接MySQL
        self._try_connect_mysql()
    
    def _try_connect_mysql(self):
        """尝试连接MySQL数据库"""
        if not self.database_url:
            print("[INFO] 未提供数据库URL，使用内存存储")
            return
        
        try:
            # 创建引擎测试连接
            engine = create_engine(self.database_url, connect_args={"connect_timeout": 5})
            with engine.connect() as conn:
                # 执行简单查询测试连接
                conn.execute(text("SELECT 1"))
            
            self.use_mysql = True
            print("[INFO] MySQL连接成功，使用数据库存储")
            
        except Exception as e:
            print(f"[WARN] MySQL连接失败: {e}")
            print("[INFO] 切换到内存存储模式")
            self.use_mysql = False
    
    def get_db_session(self):
        """获取数据库会话（仅在MySQL模式下有效）"""
        if self.use_mysql and SessionLocal:
            return SessionLocal()
        return None
    
    def create_laixin_record(self, **kwargs) -> Union[LaixinRecord, Dict]:
        """创建来信件记录"""
        if self.use_mysql:
            try:
                db = self.get_db_session()
                if db:
                    record = LaixinRecord(**kwargs)
                    db.add(record)
                    db.commit()
                    db.refresh(record)
                    db.close()
                    return record
            except Exception as e:
                print(f"[ERROR] 数据库操作失败，切换到内存存储: {e}")
                self.use_mysql = False
        
        return self.memory_storage.create_laixin_record(**kwargs)
    
    def create_net_record(self, **kwargs) -> Union[NetRecord, Dict]:
        """创建网络件记录"""
        if self.use_mysql:
            try:
                db = self.get_db_session()
                if db:
                    record = NetRecord(**kwargs)
                    db.add(record)
                    db.commit()
                    db.refresh(record)
                    db.close()
                    return record
            except Exception as e:
                print(f"[ERROR] 数据库操作失败，切换到内存存储: {e}")
                self.use_mysql = False
        
        return self.memory_storage.create_net_record(**kwargs)
    
    def update_laixin_record(self, bh: str, **kwargs) -> Union[LaixinRecord, Dict, None]:
        """更新来信件记录"""
        if self.use_mysql:
            try:
                db = self.get_db_session()
                if db:
                    record = db.query(LaixinRecord).filter(LaixinRecord.bh == bh).first()
                    if record:
                        for key, value in kwargs.items():
                            if hasattr(record, key):
                                setattr(record, key, value)
                        db.commit()
                        db.refresh(record)
                        db.close()
                        return record
                    db.close()
            except Exception as e:
                print(f"[ERROR] 数据库操作失败，切换到内存存储: {e}")
                self.use_mysql = False
        
        return self.memory_storage.update_laixin_record(bh, **kwargs)
    
    def update_net_record(self, bh: str, **kwargs) -> Union[NetRecord, Dict, None]:
        """更新网络件记录"""
        if self.use_mysql:
            try:
                db = self.get_db_session()
                if db:
                    record = db.query(NetRecord).filter(NetRecord.bh == bh).first()
                    if record:
                        for key, value in kwargs.items():
                            if hasattr(record, key):
                                setattr(record, key, value)
                        db.commit()
                        db.refresh(record)
                        db.close()
                        return record
                    db.close()
            except Exception as e:
                print(f"[ERROR] 数据库操作失败，切换到内存存储: {e}")
                self.use_mysql = False
        
        return self.memory_storage.update_net_record(bh, **kwargs)
    
    def get_laixin_records(self, filters: Dict = None) -> List[Union[LaixinRecord, Dict]]:
        """获取来信件记录列表"""
        if self.use_mysql:
            try:
                db = self.get_db_session()
                if db:
                    query = db.query(LaixinRecord).filter(LaixinRecord.is_delete == 0)
                    
                    if filters:
                        if 'date' in filters and filters['date']:
                            from sqlalchemy import func
                            query = query.filter(func.DATE(LaixinRecord.start_time) == filters['date'])
                        
                        if 'bh' in filters and filters['bh']:
                            query = query.filter(LaixinRecord.bh.ilike(f"%{filters['bh']}%"))
                        
                        if 'status' in filters and filters['status']:
                            query = query.filter(LaixinRecord.status == filters['status'])
                    
                    records = query.order_by(LaixinRecord.start_time.desc()).all()
                    
                    # 如果指定了txm参数，在内存中进行过滤
                    if filters and 'txm' in filters and filters['txm']:
                        records = [r for r in records if r.response and filters['txm'] in r.response]
                    
                    db.close()
                    return records
            except Exception as e:
                print(f"[ERROR] 数据库操作失败，切换到内存存储: {e}")
                self.use_mysql = False
        
        return self.memory_storage.get_laixin_records(filters)
    
    def get_net_records(self, filters: Dict = None) -> List[Union[NetRecord, Dict]]:
        """获取网络件记录列表"""
        if self.use_mysql:
            try:
                db = self.get_db_session()
                if db:
                    query = db.query(NetRecord).filter(NetRecord.is_delete == 0)
                    
                    if filters:
                        if 'date' in filters and filters['date']:
                            from sqlalchemy import func
                            query = query.filter(func.DATE(NetRecord.start_time) == filters['date'])
                        
                        if 'bh' in filters and filters['bh']:
                            query = query.filter(NetRecord.bh.ilike(f"%{filters['bh']}%"))
                        
                        if 'status' in filters and filters['status']:
                            query = query.filter(NetRecord.status == filters['status'])
                    
                    records = query.order_by(NetRecord.start_time.desc()).all()
                    
                    # 如果指定了ysbh参数，在内存中进行过滤
                    if filters and 'ysbh' in filters and filters['ysbh']:
                        records = [r for r in records if r.response and filters['ysbh'] in r.response]
                    
                    db.close()
                    return records
            except Exception as e:
                print(f"[ERROR] 数据库操作失败，切换到内存存储: {e}")
                self.use_mysql = False
        
        return self.memory_storage.get_net_records(filters)
    
    def delete_laixin_record(self, bh: str) -> bool:
        """逻辑删除来信件记录"""
        if self.use_mysql:
            try:
                db = self.get_db_session()
                if db:
                    record = db.query(LaixinRecord).filter(
                        LaixinRecord.bh == bh, LaixinRecord.is_delete == 0
                    ).first()
                    if record:
                        record.is_delete = 1
                        db.commit()
                        db.close()
                        return True
                    db.close()
            except Exception as e:
                print(f"[ERROR] 数据库操作失败，切换到内存存储: {e}")
                self.use_mysql = False
        
        return self.memory_storage.delete_laixin_record(bh)
    
    def delete_net_record(self, bh: str) -> bool:
        """逻辑删除网络件记录"""
        if self.use_mysql:
            try:
                db = self.get_db_session()
                if db:
                    record = db.query(NetRecord).filter(
                        NetRecord.bh == bh, NetRecord.is_delete == 0
                    ).first()
                    if record:
                        record.is_delete = 1
                        db.commit()
                        db.close()
                        return True
                    db.close()
            except Exception as e:
                print(f"[ERROR] 数据库操作失败，切换到内存存储: {e}")
                self.use_mysql = False
        
        return self.memory_storage.delete_net_record(bh)
    
    def batch_delete_laixin_records(self, bhs: List[str]) -> int:
        """批量逻辑删除来信件记录"""
        if self.use_mysql:
            try:
                db = self.get_db_session()
                if db:
                    records = db.query(LaixinRecord).filter(
                        LaixinRecord.bh.in_(bhs), LaixinRecord.is_delete == 0
                    ).all()
                    deleted_count = 0
                    for record in records:
                        record.is_delete = 1
                        deleted_count += 1
                    db.commit()
                    db.close()
                    return deleted_count
            except Exception as e:
                print(f"[ERROR] 数据库操作失败，切换到内存存储: {e}")
                self.use_mysql = False
        
        return self.memory_storage.batch_delete_laixin_records(bhs)
    
    def batch_delete_net_records(self, bhs: List[str]) -> int:
        """批量逻辑删除网络件记录"""
        if self.use_mysql:
            try:
                db = self.get_db_session()
                if db:
                    records = db.query(NetRecord).filter(
                        NetRecord.bh.in_(bhs), NetRecord.is_delete == 0
                    ).all()
                    deleted_count = 0
                    for record in records:
                        record.is_delete = 1
                        deleted_count += 1
                    db.commit()
                    db.close()
                    return deleted_count
            except Exception as e:
                print(f"[ERROR] 数据库操作失败，切换到内存存储: {e}")
                self.use_mysql = False
        
        return self.memory_storage.batch_delete_net_records(bhs)
    
    def get_laixin_stats(self) -> Dict[str, int]:
        """获取来信件统计信息"""
        if self.use_mysql:
            try:
                db = self.get_db_session()
                if db:
                    from sqlalchemy import func
                    processed_count = db.query(func.count(LaixinRecord.id)).filter(
                        LaixinRecord.is_delete == 0
                    ).scalar() or 0
                    successful_count = db.query(func.count(LaixinRecord.id)).filter(
                        LaixinRecord.is_delete == 0, LaixinRecord.status == "成功"
                    ).scalar() or 0
                    failed_count = db.query(func.count(LaixinRecord.id)).filter(
                        LaixinRecord.is_delete == 0, LaixinRecord.status == "失败"
                    ).scalar() or 0
                    db.close()
                    return {
                        'processed_count': processed_count,
                        'successful_count': successful_count,
                        'failed_count': failed_count
                    }
            except Exception as e:
                print(f"[ERROR] 数据库操作失败，切换到内存存储: {e}")
                self.use_mysql = False
        
        return self.memory_storage.get_laixin_stats()
    
    def get_net_stats(self) -> Dict[str, int]:
        """获取网络件统计信息"""
        if self.use_mysql:
            try:
                db = self.get_db_session()
                if db:
                    from sqlalchemy import func
                    processed_count = db.query(func.count(NetRecord.id)).filter(
                        NetRecord.is_delete == 0
                    ).scalar() or 0
                    successful_count = db.query(func.count(NetRecord.id)).filter(
                        NetRecord.is_delete == 0, NetRecord.status == "成功"
                    ).scalar() or 0
                    failed_count = db.query(func.count(NetRecord.id)).filter(
                        NetRecord.is_delete == 0, NetRecord.status == "失败"
                    ).scalar() or 0
                    db.close()
                    return {
                        'processed_count': processed_count,
                        'successful_count': successful_count,
                        'failed_count': failed_count
                    }
            except Exception as e:
                print(f"[ERROR] 数据库操作失败，切换到内存存储: {e}")
                self.use_mysql = False
        
        return self.memory_storage.get_net_stats()
    
    def is_using_mysql(self) -> bool:
        """检查是否正在使用MySQL"""
        return self.use_mysql


# 全局存储适配器实例
storage_adapter = None


def init_storage_adapter(database_url: str = ""):
    """初始化存储适配器"""
    global storage_adapter
    storage_adapter = StorageAdapter(database_url)
    return storage_adapter


def get_storage_adapter() -> StorageAdapter:
    """获取存储适配器实例"""
    global storage_adapter
    if storage_adapter is None:
        storage_adapter = StorageAdapter()
    return storage_adapter
